Use Excel sheets as styling templates for ZK Spreadsheet
Phoenix Wu, Engineer, Potix Corporation
November, 2010
Applicable to ZK 5.0.4 and later
- Applicable to ZK Spreadsheet 2.0.0 and later
In this article, we will demonstrate how to build a rich Excel report with ZK Spreadsheet. If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:
Live Demo
Before introduce this subject, we can watch the following video of demo.
Prepare a Template File
Before implementing ZK application, we have to prepare a template file. The following video will guide you how to:
* Define Name Ranges. * Set formula on Excel.
About naming ranges, you can refer Naming Ranges.
Load Template File into ZK
ZUL Side
Now, we begin to use this template file in our ZK application. Of course, we have to create a new zul file at this time.
The defining way of ZK Spreadsheet in ZUL file is very simple, just like following ZUL code:
<?page title="Auto Generated index.zul"?>
<window apply="org.zkdemo.controller.SpreadSheetDemoComposer">
<spreadsheet id="balance" src="/SpreadSheetDemo.xls" maxrows="20"
maxcolumns="20" height="600px" width="800px" />
</window>
Please note the line 4. It difines a few properties of Spreadsheet. The most important one at this time for us is src. If you want to load outside file -- .xls, .xlsx --, you have to define loading path on this property.
Now, we have finished definition on ZUL file at here. We will see how to operate Spreadsheet on Java side at next step.
Java Side (Composer Side)
Let's view the ZUL code again.
<?page title="Auto Generated index.zul"?>
<window apply="org.zkdemo.controller.SpreadSheetDemoComposer">
<spreadsheet id="balance" src="/SpreadSheetDemo.xls" maxrows="20"
maxcolumns="20" height="600px" width="800px" />
</window>
We have to create a Java class extends org.zkoss.zk.ui.util.GenericForwardComposer. More detail about relation of ZUL and composer class, please refer MVC in ZK.
Then, we declare a few variables and methods in this class. Let's see section of Java source code.
public class SpreadSheetDemoComposer extends GenericForwardComposer {
private Spreadsheet balance;
public void doAfterCompose(Component comp) throws Exception {
super.doAfterCompose(comp);
FinancialQuarterDAO quarterDAO = new FinancialQuarterDAO();
List<QuarterRecord> qs = quarterDAO.findAll();
for (QuarterRecord q : qs) {
drawQuarterRecord(q);
}
}
private void drawQuarterRecord(QuarterRecord record) {
int colIdx = record.getQuarterNo();
setCellValue(QuarterRecord.FIELD_QUARTER, colIdx, record.getQuarterTitle());
...... // Set cell values
}
private void setCellValue(String rangeName, int col, Object value) {
// Get range by range name(Naming Range Feature of Microsoft Excel).
Range range = Ranges.range(balance.getSelectedSheet(), rangeName);
// ZSS is 1 based, so if you want to get one cell of your range,
// you have to count by starting with 1.
Range cell = range.getCells(1, col);
cell.setEditText(value.toString());
}
}
We opreated ZK Spreadsheet in Java code. We get range from ZK Spread sheet and set value that is from our data layer into the range. About Range, it represents a cell, a row, a column, or selection of cells containing one or more contiguous blocks of cells, or a 3-D blocks of cells.
org.zkoss.zss.model.Ranges is a utility class for operating range in ZK Spreadsheet. We can get range by range reference in Ranges. Range reference is the area the Range will refer to (e.g. "A1:D4" or naming "MyCells").
Cell Mapping
In following images, you can see every naming range in my sample has 4 cells. So, why do these values can be output at correct position?
Name Range: Quarter
Name Range: LiquidAssets
Let's see method setCellValue(). It finds out range that we want to operate, and set values to cells of this range.
private void setCellValue(String rangeName, int col, Object value) {
// Get range by range name(Naming Range Feature of Microsoft Excel).
Range range = Ranges.range(balance.getSelectedSheet(), rangeName);
// ZSS is 1 based, so if you want to get one cell of your range,
// you have to count by starting with 1.
Range cell = range.getCells(1, col);
cell.setEditText(value.toString());
}
By earlier images and description, we know the range may have several cells. If you want to set value to each cell of a specified range, you have to get cell(s) from this range by position. In ZK Spreadsheet, position of cell in range is started on 1. Just like following image.
Download
Conclusion
ZK developers should have a general idea about new features of ZK Spreadsheet. From now on, ZK developers are able to access the data from ZK Spreadsheet easily. If you have any questions or suggestions, please feel free to leave comments here or post to ZK forum.
| Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License. |